
import pandas as pd
import json
import pymysql
import main1
import os
import re
from dbutils.pooled_db import PooledDB  # 注意模块名大小写‌:ml-citation{ref="1,3" data="citationList"}
import logging
from pathlib import Path
# 创建 MySQL 连接池
pool = PooledDB(
    creator=pymysql,
    maxconnections=50,
    charset='utf8mb4',
    host='rm-0jl7c97j98310zo7bho.mysql.rds.aliyuncs.com',
    user='stock_user',
    password='Man666888@2025',
    database='tracking_platform'



)

stock_sql = "SELECT id,sub_user_group_id,stock_code,stock_type,time_period,last_quantity,indicators_list,alarm_condition,trading_operations,statistics_state,analysis_method," \
            "create_time,update_time,is_deleted FROM stock_follow_record "

ds_sql = "SELECT tenant_id,consignee_name,logistics_number,collecting_price,logistics_status_desc,create_time,province,product_id FROM yc_delivery_record  " \
      "WHERE tenant_id = %s  and cancel=0 and collecting_price>0 and logistics_number IN %s"  # 单个 %s 占位符

def setup_logging():
    """设置日志"""
    logging.basicConfig(
        level=logging.INFO,
        format='%(asctime)s - %(name)s - %(levelname)s - %(message)s'
    )
    return logging.getLogger(__name__)

logger = setup_logging()

def stock_follow_query():
    results = dataTableQuerys(stock_sql)
    return  results




def insert_alarm_writing(stock_code,rules_id,tp,date_time,ad):
    # 从池中获取连接
    conn = pool.connection()
    # 执行查询
    with conn.cursor() as cursor:  # 自动关闭游标
        cursor = conn.cursor()
        try:
            params = (stock_code, rules_id, tp,date_time, ad)
            # 构建SQL
            query = "INSERT INTO {} (stock_code, stock_rules_id, time_period, trading_time, alarm_description) VALUES (%s, %s, %s, %s, %s);".format(
                'stock_alarm_notification_record')
            logger.info(f"SQL查询: {query}")
            # 执行插入
            cursor.execute(query, params)
            # 提交事务
            conn.commit()
            logger.info("数据插入成功")
            return True

        except Exception as e:
            logger.info(f"MySQL插入失败: {e}")
            return False
        finally:
            cursor.close()
            conn.close()  # 放回连接池







        # # 方法2: 批量插入
        # users_data = [
        #     ('Bob', 30, 'bob@example.com'),
        #     ('Charlie', 35, 'charlie@example.com'),
        #     ('David', 28, 'david@example.com')
        # ]
        # cursor.executemany(
        #     "INSERT INTO users (name, age, email) VALUES (?, ?, ?)",
        #     users_data
        # )








# 执行全表查询
def dataTableQuerys(sql):
    # 从池中获取连接
    conn = pool.connection()
    # 执行查询
    with conn.cursor() as cursor:  # 自动关闭游标

        try:
            cursor = conn.cursor()
            cursor.execute(sql,)  # 注意参数要包装成元组
            results = cursor.fetchall()

            # 获取列名
            column_names = [description[0] for description in cursor.description]
            # 转换为字典列表
            results_as_dicts = []
            for row in results:
                row_dict = dict(zip(column_names, row))
                results_as_dicts.append(row_dict)

            return results_as_dicts
        except Exception as e:
            logger.info(f"MySQL查询失败: {e}")
            return False
        finally:
            cursor.close()
            conn.close()  # 放回连接池



# 租户id, 执行sql，执行单号
def dataTableQuery(tenant, sql, kuaidi_tuple):
    # 从池中获取连接
    conn = pool.connection()
    # 执行查询
    with conn.cursor() as cursor:  # 自动关闭游标
        cursor = conn.cursor()
        cursor.execute(sql, (tenant, kuaidi_tuple,))  # 注意参数要包装成元组
        results = cursor.fetchall()
        cursor.close()
        conn.close()  # 放回连接池
        return results





def dfResults(results):
    df = pd.DataFrame(
        results,
        columns=['公司ID', '姓名', '物流单号', '代收金额', '快递描述', '下单时间', '区域', '产品ID']
    )
    # 单列清洗（如 content 列）
    df['姓名'] = df['姓名'].apply(clean_text)
    return df



#取下游的值
def nested_key_value(nested_dict,tenantId,key):
    # 是否存在列表里面，不存在则取000000
    if tenantId not in nested_dict:
        return nested_dict["000000"][key]

    # 获取内层字典并检查值是否存在
    tenant_dict = nested_dict[tenantId]
    if key not in tenant_dict:
        return nested_dict["000000"][key]

    return tenant_dict[key]



#判断值是否在具体的值集合里面
def value_exists_in_nested_key(nested_dict, outer_key, target_value):
    # 检查外层键是否存在
    if outer_key not in nested_dict:
        return False
    # 获取内层字典并检查值是否存在
    inner_dict = nested_dict[outer_key]
    return target_value in inner_dict.values()



# 确保非字符串字段不受影响（如数字、日期）‌:ml-citation{ref="4" data="citationList"}
def clean_text(value):
    if isinstance(value, str):
        return re.sub(r'[\n\r]+', ' ', value)
    return value


快递_名称 = "快递_汇总.txt"
代收_名称 = "代收_汇总.txt"
退货_名称 = "退货_汇总.txt"

def ensure_directory_exists(path):
    path = path.replace("/快递_汇总.txt","")
    path = path.replace("/代收_汇总.txt","")
    path = path.replace("/退货_汇总.txt","")
    Path(path).mkdir(parents=True, exist_ok=True)

def ensure_dir(directory):
    """确保指定目录存在，不存在则创建"""
    os.makedirs(directory, exist_ok=True)